﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using iOak.Domain.Resumes;
using iOak.Domain.ReportDomain;
using LateBindingApi.Core;
using Excel = NetOffice.ExcelApi;
using NetOffice.ExcelApi.Enums;
using System.Reflection;
using System.Drawing;
using iOak.Core;
using System.IO;

namespace iOak.Web.ExeclHelper
{
    public static class CollegeExeclHelper
    {
        // 创建Execl
        public static void CreateExecl(List<ResumeField> listFiled, List<ExeclDetial> listData, Type classType, Dictionary<int, string> sumNumberList)
        {
            // 创建execl application
            LateBindingApi.Core.Factory.Initialize();
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // 创建工作簿
            Excel.Workbook workBook = excelApplication.Workbooks.Add();
            workBook.Worksheets.Add();
            string sheetName = "补贴申请明细";
            string titleName = DateTime.Now.Year + "年无锡新区全国重点高校学生实习补贴申请汇总表";
            // 创建sheet
            CreateSubsidySheet(workBook, listFiled, listData, classType, sheetName, titleName, sumNumberList);
            CreateSummarySheet(workBook, listFiled, listData);
            CreateCollegesSheet(workBook, listData);

            // 设定名称
            string fileName = string.Format("实习生汇总信息{0}", ExportManager.GetDefaultExtension(excelApplication));

            // 设定路径
            string filePath = string.Format("{0}files\\ExportImport\\{1}", HttpContext.Current.Request.PhysicalApplicationPath, fileName);

            // 保存
            workBook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive);

            excelApplication.Quit();
            excelApplication.Dispose();

            CommonHelper.WriteResponseXls(filePath, fileName);

            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
        }

        #region 创建 补贴申请明细 sheet
        /// <summary>
        ///  创建 补贴申请明细 sheet
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="listFiled"></param>
        /// <param name="listData"></param>
        /// <param name="classType"></param>
        /// <param name="sheetName"></param>
        /// <param name="titleName"></param>
        /// <param name="sumNumberList"></param>
        private static void CreateSubsidySheet(Excel.Workbook workBook, List<ResumeField> listFiled, List<ExeclDetial> listData,
            Type classType, string sheetName, string titleName, Dictionary<int, string> sumNumberList)
        {
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
            workSheet.Name = sheetName;

            // 添加第一列头
            ExportManager.SetCell(workSheet, "A1", true, titleName, 12, false);
            workSheet.get_Range("A1", ExportManager.ColList[listFiled.Count - 1] + "1").MergeCells = true;
            workSheet.Rows[1, Missing.Value].RowHeight = 35;

            // 添加列表头
            int count = listFiled.Count;
            workSheet.Rows[2, Missing.Value].RowHeight = 25;
            for (int i = 0; i < count; i++)
            {
                ExportManager.SetCell(workSheet, ExportManager.ColList[i] + "2", true, listFiled[i].LabelText, 10, true);

                workSheet.Columns[i + 1, Missing.Value].NumberFormat = "@";
                if (listFiled[i].ReportWidth == null)
                {
                    workSheet.Columns[i + 1, Missing.Value].AutoFit();
                }
                else
                {
                    workSheet.Columns[i + 1, Missing.Value].ColumnWidth = listFiled[i].ReportWidth;
                }
            }

            var listProName = classType.GetProperties();

            // 添加详细信息
            for (int i = 0; i < listData.Count; i++)
            {
                // 行号
                int rowNumber = i + 3;
                workSheet.Rows[rowNumber, Missing.Value].RowHeight = 20;

                // 循环列
                foreach (var item in listProName)
                {
                    try
                    {
                        int colNumber = int.Parse(item.Name.Substring(1, item.Name.Length - 1)) - 1;
                        if (colNumber > listFiled.Count - 1)
                        {
                            continue;
                        }
                        string value = item.GetValue(listData[i], null) as string;
                        string strCell = ExportManager.ColList[colNumber] + rowNumber.ToString();
                        if (colNumber == 0)
                        {
                            value = Convert.ToString(i + 1);
                        }
                        ExportManager.SetCell(workSheet, strCell, false, value, 10, true);
                    }
                    catch
                    {
                        continue;
                    }
                }
            }

            // 添加汇总信息
            int sumRowNumber = listData.Count + 3;
            workSheet.Rows[sumRowNumber, Missing.Value].RowHeight = 20;
            ExportManager.SetCell(workSheet, "A" + sumRowNumber, true, "合计", 10, true);
            ExportManager.SetCell(workSheet, "B" + sumRowNumber, true, "", 10, true);
            ExportManager.SetCell(workSheet, "C" + sumRowNumber, true, "", 10, true);
            ExportManager.SetCell(workSheet, "D" + sumRowNumber, true, "", 10, true);
            ExportManager.SetCell(workSheet, "E" + sumRowNumber, true, "", 10, true);
            workSheet.get_Range("A" + sumRowNumber, "E" + sumRowNumber).MergeCells = true;

            ExportManager.SetCell(workSheet, "F" + sumRowNumber, true, "--", 10, true);
            ExportManager.SetCell(workSheet, "G" + sumRowNumber, true, "--", 10, true);
            ExportManager.SetCell(workSheet, "H" + sumRowNumber, true, "--", 10, true);
            ExportManager.SetCell(workSheet, "I" + sumRowNumber, true, "--", 10, true);
            ExportManager.SetCell(workSheet, "J" + sumRowNumber, true, "", 10, true);

            int sumNumber = listFiled.Where(m => m.IsSumField == true).Select(m => (int)m.ReportNumber).FirstOrDefault();

            ExportManager.SetCell(workSheet, ExportManager.ColList[sumNumber - 1] + sumRowNumber, true, sumNumberList[sumNumber], 10, true);
            ExportManager.SetCell(workSheet, "L" + sumRowNumber, true, "", 10, true);
            ExportManager.SetCell(workSheet, "M" + sumRowNumber, true, listData.Count + "家企业", 10, true);
        }
        #endregion

        #region 创建2张汇总表
        /// <summary>
        /// 创建2张汇总表
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="listFiled"></param>
        /// <param name="listData"></param>
        /// <param name="classType"></param>
        private static void CreateSummarySheet(Excel.Workbook workBook, List<ResumeField> listFiled, List<ExeclDetial> listData)
        {
            Excel.Worksheet workSheet1 = (Excel.Worksheet)workBook.Worksheets[2];
            workSheet1.Name = "企业汇总表";

            Excel.Worksheet workSheet2 = (Excel.Worksheet)workBook.Worksheets[3];
            workSheet2.Name = "联系表";

            // 添加第一列头
            ExportManager.SetCell(workSheet1, "A1", true, DateTime.Now.Year + "年无锡新区全国重点高校学生实习补贴企业汇总表", 12, false);
            workSheet1.get_Range("A1", "G1").MergeCells = true;
            workSheet1.Rows[1, Missing.Value].RowHeight = 35;

            ExportManager.SetCell(workSheet2, "A1", true, DateTime.Now.Year + "年无锡新区全国重点高校学生实习补贴企业联系表", 12, false);
            workSheet2.get_Range("A1", "G1").MergeCells = true;
            workSheet2.Rows[1, Missing.Value].RowHeight = 35;

            // 添加头信息
            workSheet1.Rows[2, Missing.Value].RowHeight = 25;
            workSheet2.Rows[2, Missing.Value].RowHeight = 25;
            for (int i = 0; i < 7; i++)
            {
                workSheet1.Columns[i + 1, Missing.Value].NumberFormat = "@";
                workSheet2.Columns[i + 1, Missing.Value].NumberFormat = "@";
            }
            ExportManager.SetCell(workSheet1, "A2", true, "序号", 10, true);
            ExportManager.SetCell(workSheet1, "B2", true, "实习单位", 10, true);
            ExportManager.SetCell(workSheet1, "C2", true, "开户银行", 10, true);
            ExportManager.SetCell(workSheet1, "D2", true, "帐号", 10, true);
            ExportManager.SetCell(workSheet1, "E2", true, "补贴金额", 10, true);
            ExportManager.SetCell(workSheet1, "F2", true, "补贴人数", 10, true);
            ExportManager.SetCell(workSheet1, "G2", true, "备注", 10, true);
            workSheet1.Columns[1, Missing.Value].ColumnWidth = 5.5;
            workSheet1.Columns[2, Missing.Value].ColumnWidth = 28;
            workSheet1.Columns[3, Missing.Value].ColumnWidth = 25;
            workSheet1.Columns[4, Missing.Value].ColumnWidth = 25;
            workSheet1.Columns[5, Missing.Value].ColumnWidth = 10;
            workSheet1.Columns[6, Missing.Value].ColumnWidth = 10;
            workSheet1.Columns[7, Missing.Value].ColumnWidth = 15;

            ExportManager.SetCell(workSheet2, "A2", true, "序号", 10, true);
            ExportManager.SetCell(workSheet2, "B2", true, "实习单位", 10, true);
            ExportManager.SetCell(workSheet2, "C2", true, "联系人", 10, true);
            ExportManager.SetCell(workSheet2, "D2", true, "联系电话", 10, true);
            ExportManager.SetCell(workSheet2, "E2", true, "补贴金额", 10, true);
            ExportManager.SetCell(workSheet2, "F2", true, "补贴人数", 10, true);
            ExportManager.SetCell(workSheet2, "G2", true, "备注", 10, true);
            workSheet2.Columns[1, Missing.Value].ColumnWidth = 5.25;
            workSheet2.Columns[2, Missing.Value].ColumnWidth = 28;
            workSheet2.Columns[3, Missing.Value].ColumnWidth = 8.75;
            workSheet2.Columns[4, Missing.Value].ColumnWidth = 21.5;
            workSheet2.Columns[5, Missing.Value].ColumnWidth = 10;
            workSheet2.Columns[6, Missing.Value].ColumnWidth = 10;
            workSheet2.Columns[7, Missing.Value].ColumnWidth = 15;

            // 分组添加详细信息
            var groupList = listData.OrderBy(o=>o.CompanyName).GroupBy(m => m.CompanyName);
            int rowNumber = 3;
            int index = 1;
            decimal allSum = 0;
            int listNumber = 0;
            foreach (var groupItem in groupList)
            {
                var currentList = listData.Where(m => m.CompanyName.Equals(groupItem.Key));
                var sumList = currentList.Where(m => m.C6.Equals("通过"));
                listNumber += sumList.Count();
                var sunNumber = Math.Round(sumList.Sum(m => decimal.Parse(m.C11)), 2);
                allSum += sunNumber;
                var currentItem = currentList.FirstOrDefault();
                ExportManager.SetCell(workSheet1, "A" + rowNumber, false, index.ToString(), 10, true);
                ExportManager.SetCell(workSheet1, "B" + rowNumber, false, groupItem.Key, 10, true);
                ExportManager.SetCell(workSheet1, "C" + rowNumber, false, currentItem.BankName, 10, true);
                ExportManager.SetCell(workSheet1, "D" + rowNumber, false, currentItem.BankAccountNo, 10, true);
                ExportManager.SetCell(workSheet1, "E" + rowNumber, false, sunNumber.ToString(), 10, true);
                ExportManager.SetCell(workSheet1, "F" + rowNumber, false, sumList.Count().ToString(), 10, true);
                ExportManager.SetCell(workSheet1, "G" + rowNumber, false, "", 10, true);

                ExportManager.SetCell(workSheet2, "A" + rowNumber, false, index.ToString(), 10, true);
                ExportManager.SetCell(workSheet2, "B" + rowNumber, false, groupItem.Key, 10, true);
                ExportManager.SetCell(workSheet2, "C" + rowNumber, false, currentItem.ContactPerson, 10, true);
                ExportManager.SetCell(workSheet2, "D" + rowNumber, false, currentItem.ContactPhone, 10, true);
                ExportManager.SetCell(workSheet2, "E" + rowNumber, false, sunNumber.ToString(), 10, true);
                ExportManager.SetCell(workSheet2, "F" + rowNumber, false, sumList.Count().ToString(), 10, true);
                ExportManager.SetCell(workSheet2, "G" + rowNumber, false, "", 10, true);
                rowNumber++;
                index++;
            }

            workSheet1.Rows[rowNumber, Missing.Value].RowHeight = 20;
            ExportManager.SetCell(workSheet1, "A" + rowNumber, true, "合计", 10, true);
            ExportManager.SetCell(workSheet1, "B" + rowNumber, true, "", 10, true);
            workSheet1.get_Range("A" + rowNumber, "B" + rowNumber).MergeCells = true;
            ExportManager.SetCell(workSheet1, "C" + rowNumber, true, "", 10, true);
            ExportManager.SetCell(workSheet1, "D" + rowNumber, true, "", 10, true);
            ExportManager.SetCell(workSheet1, "E" + rowNumber, true, allSum.ToString(), 10, true);
            ExportManager.SetCell(workSheet1, "F" + rowNumber, true, listNumber.ToString(), 10, true);
            ExportManager.SetCell(workSheet1, "G" + rowNumber, true, "", 10, true);

            workSheet2.Rows[rowNumber, Missing.Value].RowHeight = 20;
            ExportManager.SetCell(workSheet2, "A" + rowNumber, true, "合计", 10, true);
            ExportManager.SetCell(workSheet2, "B" + rowNumber, true, "", 10, true);
            workSheet2.get_Range("A" + rowNumber, "B" + rowNumber).MergeCells = true;
            ExportManager.SetCell(workSheet2, "C" + rowNumber, true, "", 10, true);
            ExportManager.SetCell(workSheet2, "D" + rowNumber, true, "", 10, true);
            ExportManager.SetCell(workSheet2, "E" + rowNumber, true, allSum.ToString(), 10, true);
            ExportManager.SetCell(workSheet2, "F" + rowNumber, true, listNumber.ToString(), 10, true);
            ExportManager.SetCell(workSheet2, "G" + rowNumber, true, "", 10, true);
        }
        #endregion

        #region 添加高校名录汇总
        /// <summary>
        /// 添加高校名录汇总
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="listData"></param>
        private static void CreateCollegesSheet(Excel.Workbook workBook, List<ExeclDetial> listData)
        {

            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[4];
            workSheet.Name = "高校名录";

            // 添加第一列头
            ExportManager.SetCell(workSheet, "A1", true, "无锡新区高校毕业生就业、实习基地共建名录", 12, false);
            workSheet.get_Range("A1", "C1").MergeCells = true;
            workSheet.Rows[1, Missing.Value].RowHeight = 35;

            // 添加列表头
            workSheet.Rows[2, Missing.Value].RowHeight = 25;
            for (int i = 0; i < 3; i++)
            {
                workSheet.Columns[i + 1, Missing.Value].NumberFormat = "@";
            }
            workSheet.Columns[1, Missing.Value].ColumnWidth = 8;
            workSheet.Columns[2, Missing.Value].ColumnWidth = 40;
            workSheet.Columns[3, Missing.Value].ColumnWidth = 26.38;
            ExportManager.SetCell(workSheet, "A2", true, "序号", 10, true);
            ExportManager.SetCell(workSheet, "B2", true, "院校", 10, true);
            ExportManager.SetCell(workSheet, "C2", true, "备注", 10, true);

            // 添加详细
            int rowNumber = 3;
            int index = 1;
            var groupList = listData.OrderBy(o=>o.C8).GroupBy(m => m.C8).Select(s => s.Key);
            foreach (var item in groupList)
            {
                ExportManager.SetCell(workSheet, "A" + rowNumber, false, index.ToString(), 10, true);
                ExportManager.SetCell(workSheet, "B" + rowNumber, false, item, 10, true);
                ExportManager.SetCell(workSheet, "C" + rowNumber, false, "", 10, true);
                rowNumber++;
                index++;
            }

            ExportManager.SetCell(workSheet, "C" + rowNumber, false, "制表日期：" + DateTime.Now.ToString("yyyy年MM月dd日"), 10, false);
        }
        #endregion
    }
}